package tv.accedo.achi.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Component;

import tv.accedo.achi.model.Achi;
import tv.accedo.achi.model.Badge;
import tv.accedo.achi.model.Camp;
import tv.accedo.achi.model.User;

/**
 * A dao class with user model database operations.
 * @author Bo Liang
 * 
 */
@Component
public class UserDao {

	private SessionFactory sessionFactory; // injection
	private User user; // injection
	private Achi achi; //injection
	private AchiDao achiDao; //injection
	private Badge badge; //injection
	private Camp camp; //injection
	
	/**
	 * Get user's application serial number by using user id.
	 * <p>by using user id, this function returns the application serial number 
	 * which applications that user already downloaded or bought.
	 * @param user_id	user id
	 * @return			List with application serial number
	 */
	public List<String> getAppsnsByUseid(int user_id) {
		List<String> appsnList = new ArrayList<String>(); 
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			Query query = session.createSQLQuery("select distinct app_sn from t_user_achi where user_id = ? order by app_sn desc")
					.setParameter(0, user_id); 
			appsnList = query.list(); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return appsnList; 
	}
	
	/**
	 * Get user object by using user id.
	 * @param user_id	user id
	 * @return			user object
	 */
	public User getUserByUserid(int user_id) {
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			Query query = session.createQuery("from User user where user.id = ?")
					.setParameter(0, user_id); 
			user = (User) query.uniqueResult(); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}	
		return user;
	}
	
	/**
	 * Get friends' id by using user id.
	 * @param user_id	user id
	 * @return			List with friends' id
	 */
	public List<Integer> getFriendsidListByUserid(int user_id) {
		List<Integer> fri_idList = new ArrayList<Integer>(); 
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			Query query = session.createSQLQuery("select fri_id from t_user_fri where user_id = ?")
			   .setParameter(0, user_id);
			fri_idList = query.list(); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return fri_idList; 
	}
	
	/**
	 * Add achievement to user when user unlock this achievement.
	 * @param username	user name
	 * @param achi_sn	achievement serial number
	 * @return 			string flag : new/old
	 */
	public String addAchiToUser(String username, String achi_sn) {
		String flag = null;
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			session.beginTransaction();
			
			// get achi_id, app_sn and user_id by username and achi_sn
			Query query = session.createQuery("from Achi achi where achi.achi_sn = ?")
					.setParameter(0, achi_sn); 
			achi = (Achi) query.uniqueResult(); 
			int achi_id = achi.getId(); 
			String app_sn = achi.getApp_sn(); 
			
			query = session.createQuery("from User user where user.username = ?")
					.setParameter(0, username); 
			user = (User) query.uniqueResult(); 
			int user_id = user.getId(); 
			
			// if this user has already unlocked this achievement before, just make achi_num plus 1 
			if (user.getAchis().contains(achi)) {
				session.createSQLQuery("update t_user_achi set achi_num=achi_num+1, app_sn = ? where user_id = ? and achi_id = ?")
					   .setParameter(0, app_sn)
				       .setParameter(1, user_id)
				       .setParameter(2, achi_id)
				       .executeUpdate();
				session.getTransaction().commit();
				flag = "old"; 
				
			/*  if this user has not unlocked this achievement before,
			 *  Firstly, add this achievement into this user,
			 *  Secondly, update this piece of record with the app_sn which this achievement was belonged to.
			 */ 
			} else {
				user.getAchis().add(achi);
				session.save(user);
				session.getTransaction().commit();
				
				session.createSQLQuery("update t_user_achi set app_sn = ? where user_id = ? and achi_id = ?")
				   .setParameter(0, app_sn)
				   .setParameter(1, user_id)
				   .setParameter(2, achi_id)
			       .executeUpdate();
				flag = "new"; 
			}	
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return flag; 
	}
	
	/**
	 * Increase the number of achievement in the specific badge that user have unlocked.
	 * @param username	user name
	 * @param badge_id	badge id
	 * @return			true/false
	 */
	public boolean addBadgeNumToUser(String username, int badge_id) {
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			session.beginTransaction();
			// get badge object by using badge id
			Query query = session.createQuery("from Badge badge where badge.id = ?")
					.setParameter(0, badge_id); 
			badge = (Badge) query.uniqueResult();   
			
			// get user object and user id by using user name
			query = session.createQuery("from User user where user.username = ?")
					.setParameter(0, username); 
			user = (User) query.uniqueResult(); 
			int user_id = user.getId(); 
			
			// if user already has this badge, just make badge_num add 1
			if (user.getBadges().contains(badge)) {
				session.createSQLQuery("update t_user_badge set badge_num=badge_num+1 where user_id = ? and badge_id = ?")
				       .setParameter(0, user_id)
				       .setParameter(1, badge_id)
				       .executeUpdate();
				session.getTransaction().commit();
			// if user does not have this badge record, add a new badge
			} else {
				user.getBadges().add(badge);
				session.save(user);
				session.getTransaction().commit();
				
				// count achievement num of this badge and assign this value to badge total field
				query = session.createSQLQuery("select count(achi_id) from t_badge_achi where badge_id = ?")
							   .setParameter(0, badge.getId()); 
				int badge_total = ((BigInteger) query.uniqueResult()).intValue(); 
				
				session.createSQLQuery("update t_user_badge set badge_total = ? where user_id = ? and badge_id = ?")
			       	   .setParameter(0, badge_total)
					   .setParameter(1, user_id)
			           .setParameter(2, badge_id)
			           .executeUpdate();
				
			}
			// if badge_total equals badge_num, it means this user unlocked this badge, and then assign the unlock_flag to 1
			session.createSQLQuery("update t_user_badge set unlock_flag = 1 where badge_total = badge_num and user_id = ? and badge_id = ?")
		       .setParameter(0, user_id)
		       .setParameter(1, badge_id)
		       .executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return true; 
	}
	
	/**
	 * Increase the number of achievement in the specific campaign that user have unlocked.
	 * @param username	user name
	 * @param camp_id	camp id
	 * @return			true/false
	 */
	public boolean addCampNumToUser(String username, int camp_id) {
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			session.beginTransaction();
			// get campaign object by using campaign id
			Query query = session.createQuery("from Camp camp where camp.id = ?")
					.setParameter(0, camp_id); 
			camp = (Camp) query.uniqueResult(); 
			
			// get user object and user id by using user name
			query = session.createQuery("from User user where user.username = ?")
					.setParameter(0, username); 
			user = (User) query.uniqueResult(); 
			int user_id = user.getId(); 
			
			// if user already has this badge, just make camp_num add 1
			if (user.getCamps().contains(camp)) {
				session.createSQLQuery("update t_user_camp set camp_num=camp_num+1 where user_id = ? and camp_id = ?")
				       .setParameter(0, user_id)
				       .setParameter(1, camp_id)
				       .executeUpdate();
				session.getTransaction().commit();
			// if user does not have this campaign record, add a new campaign
			} else {
				user.getCamps().add(camp);
				session.save(user);
				session.getTransaction().commit();
				
				query = session.createSQLQuery("select count(achi_id) from t_camp_achi where camp_id = ?")
							   .setParameter(0, camp.getId()); 
				int camp_total = ((BigInteger) query.uniqueResult()).intValue(); 
				
				// count achievement num of this campaign and assign this value to campaign total field
				session.createSQLQuery("update t_user_camp set camp_total = ? where user_id = ? and camp_id = ?")
			       	   .setParameter(0, camp_total)
					   .setParameter(1, user_id)
			           .setParameter(2, camp_id)
			           .executeUpdate();
				
			}
			// if camp_total equals camp_num, it means this user finished this campaign, and then assign the unlock_flag to 1
			session.createSQLQuery("update t_user_camp set unlock_flag = 1 where camp_total = camp_num and user_id = ? and camp_id = ?")
		       .setParameter(0, user_id)
		       .setParameter(1, camp_id)
		       .executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return true; 
	}

	/**
	 * Get unique user object by user name.
	 * @param username	user name
	 * @return 			user object
	 */
	public User getUserByUsername(String username) {
		Session session = null; 
		try {
//			session = SessionFactoryUtil.getSessionFactory().openSession(); 
			session = sessionFactory.openSession();
			Query query = session.createQuery("from User user where user.username = ?")
					.setParameter(0, username); 
			user = (User) query.uniqueResult(); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
//			SessionFactoryUtil.closeSession(session);
			session.close();
		}
		return user; 
	}
	
	/**
	 * Get user List by input string for fuzzy query.
	 * @param inputString	user name
	 * @return 				User List
	 */
	public List<User> getUsersByString(String inputString) {
		List<User> userList = new ArrayList<User>();
		Session session = null; 
		try {
//			session = SessionFactoryUtil.getSessionFactory().openSession(); 
			session = sessionFactory.openSession();
			Query query = session.createQuery("from User user where user.username like ?")
					.setParameter(0, "%" + inputString + "%"); 
			userList = query.list(); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
//			SessionFactoryUtil.closeSession(session);
			session.close();
		}
		return userList; 
	}
	
	/**
	 * Get all user objects.
	 * @return user List
	 */
	public List<User> getAllUsers() {
		List<User> userList = new ArrayList<User>(); 
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			Query query = session.createQuery("from User user order by user.score asc");
			userList = query.list(); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return userList; 
	}
	
	/**
	 * Check whether this user has unlocked the specific achievement.
	 * @param username	user name
	 * @param achi_sn	achievement serial number
	 * @return			true/false
	 */
	public boolean checkUserAndAchi(String username, String achi_sn) {
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			Query query = session.createQuery("from User user where user.username = ?")
				.setParameter(0, username); 
			user = (User) query.uniqueResult(); 
			achi = achiDao.getAchiByAchisn(achi_sn);
			if (user.getAchis().contains(achi)) {
				return true;
			} else {
				return false; 
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return false;

	}
	/**
	 * Add score to the user when user unlocked the achievement in the first time.
	 * @param username		user name
	 * @param achi_score	achievement's score points
	 * @return				true/false
	 */
	
	public boolean addScoreToUser(String username, long achi_score) {
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			session.beginTransaction();
			session.createSQLQuery("update t_user set score = score + ? where username = ?")
		       .setParameter(0, achi_score)
		       .setParameter(1, username)
		       .executeUpdate();
			session.getTransaction().commit();
		} catch (Exception e) {
			session.getTransaction().rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		return true;
	}
	
	public int getAppNumByUseridAndAppsn(int user_id, String app_sn) {
		int appNum = 0;; 
		Session session = null; 
		try {
			session = sessionFactory.openSession();
			Query query = session.createSQLQuery("select count(app_sn) from t_user_achi where user_id = ? and app_sn = ?")
					      .setParameter(0, user_id)
					      .setParameter(1, app_sn);
			appNum = ((Number)query.uniqueResult()).intValue(); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return appNum;
	}
	
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	@Resource
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	public User getUser() {
		return user;
	}

	@Resource
	public void setUser(User user) {
		this.user = user;
	}

	public Achi getAchi() {
		return achi;
	}


	@Resource
	public void setAchi(Achi achi) {
		this.achi = achi;
	}

	public AchiDao getAchiDao() {
		return achiDao;
	}

	@Resource
	public void setAchiDao(AchiDao achiDao) {
		this.achiDao = achiDao;
	}

	public Badge getBadge() {
		return badge;
	}

	@Resource
	public void setBadge(Badge badge) {
		this.badge = badge;
	}

	public Camp getCamp() {
		return camp;
	}

	@Resource
	public void setCamp(Camp camp) {
		this.camp = camp;
	}
	
	
}
